use std::fmt::Display;

use sqlx::{PgPool};

use crate::{base::errors::AppError};

pub mod user_repository;
pub mod role_repository;
pub mod menu_repository;

#[derive(Debug, Clone)]
pub enum ParamValue {
    String(String),
    Int32(i32),
    Int64(i64),
    Bool(bool),
}

impl Display for ParamValue {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        match self {
            ParamValue::String(s) => write!(f, "{}", s),
            ParamValue::Int32(i) => write!(f, "{}", i),
            ParamValue::Int64(i) => write!(f, "{}", i),
            ParamValue::Bool(b) => write!(f, "{}", b)
        }
    }
}

impl From<String> for ParamValue {
    fn from(s: String) -> Self {
        ParamValue::String(s)
    }
}

impl From<&str> for ParamValue {
    fn from(s: &str) -> Self {
        ParamValue::String(s.to_string())
    }
}

impl From<i32> for ParamValue {
    fn from(i: i32) -> Self {
        ParamValue::Int32(i)
    }
}

impl From<i64> for ParamValue {
    fn from(i: i64) -> Self {
        ParamValue::Int64(i)
    }
}

impl From<bool> for ParamValue {
    fn from(b: bool) -> Self {
        ParamValue::Bool(b)
    }
}

pub struct QueryBuilder {
    base_sql: Option<String>,
    conditions: Vec<String>,
    bind_params: Vec<ParamValue>,
    param_count: usize,
    order_by: Option<String>,
    current: Option<i32>,
    size: Option<i32>,
}

impl QueryBuilder {
    pub fn new() -> Self {
        QueryBuilder {
            base_sql: None,
            conditions: Vec::new(), 
            bind_params: Vec::new(), 
            param_count: 0,
            order_by: None,
            current: None,
            size: None,
        }
    }

    // 添加字符串条件
    fn add_condition<T: Into<ParamValue>>(&mut self, field: &str, value: Option<T>, operator: &str) -> &mut Self {
        if let Some(val) = value {
            self.param_count += 1;
            self.conditions.push(format!("{} {} ${}", field, operator, self.param_count));

            let param_value: ParamValue = val.into();

            // if operator.to_uppercase() == "LIKE" {
            //     self.bind_params.push(format!("%{}%", val.to_string())); // 模糊查询
            // } else {
            //     self.bind_params.push(val.to_string());
            // }
            if operator.to_uppercase() == "LIKE" {
                if let ParamValue::String(s) = param_value {
                    self.bind_params.push(ParamValue::String(format!("%{}%", s)));
                } else {
                    // 对于非字符串类型，尝试转换为字符串进行模糊匹配
                    self.bind_params.push(ParamValue::String(format!("%{}%", param_value.to_string())));
                }
            } else {
                self.bind_params.push(param_value);
            }
        }
        self
    }

    pub fn sql(mut self, base_sql: String) -> Self {
        self.base_sql = Some(base_sql);
        self
    }

    // 模糊匹配
    pub fn like(mut self, field: &str, value: Option<&str>) -> Self {
        self.add_condition(field, value, "LIKE");
        self
    }

    // 添加精确匹配条件
    pub fn equal<T: Into<ParamValue>>(mut self, field: &str, value: Option<T>) -> Self {
        self.add_condition(field, value, "=");
        self
    }

    // 添加不等于条件
    pub fn not_equal<T: Into<ParamValue>>(mut self, field: &str, value: Option<T>) -> Self {
        self.add_condition(field, value, "<>");
        self
    }
    pub fn greater_than<T: Into<ParamValue>>(mut self, field: &str, value: Option<T>) -> Self {
        self.add_condition(field, value, ">");
        self
    }

    pub fn less_than<T: Into<ParamValue>>(mut self, field: &str, value: Option<T>) -> Self {
        self.add_condition(field, value, "<");
        self
    }

    pub fn greater_than_or_equal_to<T: Into<ParamValue>>(mut self, field: &str, value: Option<T>) -> Self {
        self.add_condition(field, value, ">=");
        self
    }

    pub fn less_than_or_equal_to<T: Into<ParamValue>>(mut self, field: &str, value: Option<T>) -> Self {
        self.add_condition(field, value, "<=");
        self
    }

    pub fn in_list<T: Into<ParamValue> + Clone>(mut self, field: &str, values: Option<Vec<T>>) -> Self {
        if let Some(vals) = values {
            if !vals.is_empty() {
                // 记录起始位置
                let start_param = self.param_count + 1;

                // 生成占位符
                self.param_count += 1;
                let placeholders = (0..vals.len())
                    .map(|i| format!("${}", start_param + i))
                    .collect::<Vec<_>>()
                    .join(", ");

                self.conditions.push(format!("{} IN ({})", field, placeholders));

                for val in vals.clone() {
                    self.bind_params.push(val.into());
                }
                // self.param_count += vals.len() - 1;
                self.param_count += vals.len()
            }
        }
        self
    }

    // 设置排序
    pub fn desc(mut self, order_by: &str) -> Self {
        self.order_by = Some(format!("{} DESC", order_by));
        self
    }

    // 设置分页
    pub fn paginate(mut self, current: i32, size: i32) -> Self {
        self.current = Some(current);
        self.size = Some(size);
        self
    }

    fn build_sql(&self) -> Result<String, AppError> {
        let mut query_str = self.base_sql
            .as_ref()
            .ok_or(AppError::Runtime("基础SQL语句不能为空".to_string()))?
            .clone();

        // 添加 WHERE 子句
        if !self.conditions.is_empty() {
            query_str.push_str(" WHERE ");
            query_str.push_str(&self.conditions.join(" AND "));
        }

        // 添加 ORDER BY 子句
        if let Some(order_by) = &self.order_by {
            query_str.push_str(&format!(" ORDER BY {}", order_by));
        }

        // 添加分页
        if let (Some(current), Some(size)) = (self.current, self.size) {
            let offset = (current - 1) * size;
            query_str.push_str(&format!(" LIMIT {} OFFSET {}", size, offset));
        }

        Ok(query_str)
    }

    pub async fn query_all<'a, T>(self, db_pool: &PgPool) -> Result<Vec<T>, AppError> 
    where 
        T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
    {
        let sql = self.build_sql()
            .map_err(|e| AppError::Runtime(format!("创建SQL失败: {}", e)))?;
        
        // 创建基础查询对象
        let mut query = sqlx::query_as::<_, T>(&sql);

        // 绑定参数
        for param in self.bind_params {
            // query = query.bind(param);
            match param {
                ParamValue::String(s) => query = query.bind(s),
                ParamValue::Int32(i) => query = query.bind(i),
                ParamValue::Int64(i) => query = query.bind(i),
                ParamValue::Bool(b) => query = query.bind(b)
            }
        }

        query
            .fetch_all(db_pool)
            .await
            .map_err(|e| AppError::Database(e.to_string()))
    }

    pub async fn query_one<'a, T>(self, db_pool: &PgPool) -> Result<T, AppError>
    where 
        T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
    {
        let sql = self.build_sql()
            .map_err(|e| AppError::Runtime(format!("创建SQL失败: {}", e)))?;
        
        // 创建基础查询对象
        let mut query = sqlx::query_as::<_, T>(&sql);

        // 绑定参数
        for param in self.bind_params {
            match param {
                ParamValue::String(s) => query = query.bind(s),
                ParamValue::Int32(i) => query = query.bind(i),
                ParamValue::Int64(i) => query = query.bind(i),
                ParamValue::Bool(b) => query = query.bind(b)
            }
        }

        query
            .fetch_one(db_pool)
            .await
            .map_err(|e| AppError::Database(e.to_string()))
    }

    pub async fn query_count(self, db_pool: &PgPool) -> Result<i64, AppError>
    {
        let sql = self.build_sql()
            .map_err(|e| AppError::Runtime(format!("创建SQL失败: {}", e)))?;
        
        // 创建基础查询对象
        let mut query = sqlx::query_scalar(&sql);

        // 绑定参数
        for param in self.bind_params {
            match param {
                ParamValue::String(s) => query = query.bind(s),
                ParamValue::Int32(i) => query = query.bind(i),
                ParamValue::Int64(i) => query = query.bind(i),
                ParamValue::Bool(b) => query = query.bind(b)
            }
        }

        query
            .fetch_one(db_pool)
            .await
            .map_err(|e| AppError::Database(e.to_string()))
    }

    // // 构建完整的 SQL 查询
    // pub fn build(self) -> Result<(String, Vec<String>), AppError> {
    //     let mut query_str = self.base_sql
    //         .ok_or(AppError::Runtime("基础SQL语句不能为空".to_string()))?;
    //     let mut bind_params = self.bind_params;

    //     // 添加 WHERE 子句
    //     if !self.conditions.is_empty() {
    //         query_str.push_str(" WHERE ");
    //         query_str.push_str(&self.conditions.join(" AND "));
    //     }

    //     // 添加 ORDER BY 子句
    //     if let Some(order_by) = self.order_by {
    //         query_str.push_str(&format!(" ORDER BY {}", order_by));
    //     }

    //     // 处理分页：直接嵌入数值到SQL中，避免类型错误
    //     if let (Some(current), Some(size)) = (self.current, self.size) {
    //         let offset = (current - 1) * size;
    //         query_str.push_str(&format!(" LIMIT {} OFFSET {}", size, offset));
    //     }

    //     Ok((query_str, bind_params))
    // }
}